﻿using BonjourQuotation.DataAccess.Entity_Framework;
using BonjourQuotation.ObjectModel.AccountData;
using BonjourQuotation.ObjectModel.BusinessData;
using BonjourQuotation.ObjectModel.ErrorData;
using BonjourQuotation.ObjectModel.LoggerData;
using BonjourQuotation.ObjectModel.GuideData;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BonjourQuotation.DataAccess.Repositories
{
    public class GuideRepository : IGuideRepository
    {
        private readonly BonDataEntities _context = new BonDataEntities();
        private readonly log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public ErrorBInfo CreateGuideCompanyInfo(GuideCompanyInfoBData GuideData, ref int GuideID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                int maxGroupId = _context.tblGuides.Select(Guide => Guide.GuideID).Max();
                tblGuide NewGuide = new tblGuide()
                {
                    GuideID = maxGroupId + 1,
                    FirstName = GuideData.FirstName,
                    LastName = GuideData.LastName,
                    MiddleName = GuideData.MiddleName,
                    DateHired = GuideData.DateHired,
                    Active = GuideData.Active,
                    IsFind = GuideData.IsFind,
                    Phone = GuideData.Phone,
                    Fax = GuideData.Fax,
                    RegionID = GuideData.SelectedRegionID,
                    AreaID = GuideData.SelectedAreaID,
                    Salary = GuideData.Salary,
                    Note = GuideData.Note,
                };
                GuideID = maxGroupId + 1;
                _context.tblGuides.Add(NewGuide);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public ErrorBInfo CreateGuidePersonalInfo(GuidePersonalInfoBData GuideData, ref int GuideID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                int maxGroupId = _context.tblGuides.Select(Guide => Guide.GuideID).Max();
                tblGuide NewGuide = new tblGuide()
                {
                    GuideID = maxGroupId + 1,
                    SocialSecurityNumber = GuideData.SocialSecurityNumber,
                    Sex = GuideData.SelectedSex,
                    Address = GuideData.Address,
                    City = (from city in _context.tblCities where city.CityID == GuideData.CityID select city.City).SingleOrDefault(),
                    CityID = GuideData.CityID,
                    StateID = GuideData.SelectedStateID,
                    PostalCode = GuideData.PostalCode,
                    Country = GuideData.SelectedCountryID,
                    LCountryID = GuideData.SelectedLCountryID,
                    Birthdate = GuideData.BirthDate,
                    EmailName = GuideData.EMail,
                    SpouseName = GuideData.SpouseName,
                    EmrgcyContactName = GuideData.EmrgcyContactName,
                    EmrgcyContactPhone = GuideData.EmrgcyContactPhone,
                };
                GuideID = maxGroupId + 1;
                _context.tblGuides.Add(NewGuide);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public ManageGuidesGridBData LoadGuides(int PageNumber)
        {
            ManageGuidesGridBData Guides = new ManageGuidesGridBData();
            Guides.Rows = new List<ManageGuidesGridRowBData>();
            Guides.Rows = (from Guide in _context.tblGuides
                           orderby Guide.FirstName
                           select new ManageGuidesGridRowBData()
                              {
                                  GuideID = Guide.GuideID,
                                  GuideName = Guide.FirstName,
                                  GuideLastName = Guide.LastName,
                                  City = Guide.City,
                                  Active = Guide.Active,
                                  IsFind = Guide.IsFind,
                              }).Take(PageNumber * ManageGuidesGridBData.PageCount).ToList();

            Guides.City = (from city in _context.tblCities
                           where city.Active == true
                           orderby city.City
                           select new
                           {
                               Key = city.CityID,
                               Value = city.City
                           }).ToDictionary(o => o.Key, o => o.Value);
            Guides.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            Guides.IsFind = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Guides;
        }
        public ManageGuidesGridBData LoadSearchedGuides(ManageGuidesGridBData Data)
        {
            var FilterGuide = _context.tblGuides.AsQueryable();
            if (Data.GuideName != null && Data.GuideName.Length > 0)
                FilterGuide = FilterGuide.Where(cust => cust.FirstName.Contains(Data.GuideName.Trim()));
            if (Data.GuideLastName != null && Data.GuideLastName.Length > 0)
                FilterGuide = FilterGuide.Where(cust => cust.LastName.Contains(Data.GuideLastName.Trim()));
            if (Data.SelectedCity != null)
                FilterGuide = FilterGuide.Where(cust => cust.City == Data.SelectedCity);
            if (Data.SelectedActive != null)
                FilterGuide = FilterGuide.Where(cust => cust.Active == Data.SelectedActive);
            if (Data.SelectedIsFind != null)
                FilterGuide = FilterGuide.Where(cust => cust.IsFind == Data.SelectedIsFind);
            ManageGuidesGridBData AllGuides;
            AllGuides = new ManageGuidesGridBData();
            AllGuides.Rows = new List<ManageGuidesGridRowBData>();
            AllGuides.Rows = (from Guide in FilterGuide
                              orderby Guide.FirstName
                              select new ManageGuidesGridRowBData()
                                 {
                                     GuideID = Guide.GuideID,
                                     GuideName = Guide.FirstName,
                                     GuideLastName = Guide.LastName,
                                     City = Guide.City,
                                     Active = Guide.Active,
                                     IsFind = Guide.IsFind,
                                 }).Take(Data.PageNum * ManageGuidesGridBData.PageCount).ToList();

            AllGuides.City = (from city in _context.tblCities
                              where city.Active == true
                              orderby city.City
                              select new
                              {
                                  Key = city.CityID,
                                  Value = city.City
                              }).ToDictionary(o => o.Key, o => o.Value);
            return AllGuides;
        }
        public GuideCompanyInfoBData LoadGuideCompanyInfoByID(int GuideID)
        {
            GuideCompanyInfoBData GuideData = null;
            if (GuideID == 0)
                GuideData = new GuideCompanyInfoBData();
            else
                GuideData = (from Guide in _context.tblGuides
                             where Guide.GuideID == GuideID
                             select new GuideCompanyInfoBData
                              {
                                  GuideID = Guide.GuideID,
                                  FirstName = Guide.FirstName,
                                  LastName = Guide.LastName,
                                  MiddleName = Guide.MiddleName,
                                  DateHired = Guide.DateHired,
                                  Active = Guide.Active,
                                  IsFind = Guide.IsFind,
                                  Phone = Guide.Phone,
                                  SelectedLCountryID = Guide.LCountryID,
                                  Fax = Guide.Fax,
                                  SelectedRegionID = Guide.RegionID,
                                  SelectedAreaID = Guide.AreaID,
                                  Note = Guide.Note,
                                  Salary = Guide.Salary,
                              }).SingleOrDefault();
            GuideData.RegionID = (from reg in _context.tblRegions where reg.Active == true select new { Key = reg.RegionID, Value = reg.RegionDesc }).ToDictionary(o => o.Key, o => o.Value);
            GuideData.AreaID = (from area in _context.tblAreas where area.Active == true select new { Key = area.AreaID, Value = area.AreaDesc }).ToDictionary(o => o.Key, o => o.Value);
            return GuideData;
        }
        public GuidePersonalInfoBData LoadGuidePersonalInfoByID(int GuideID)
        {
            GuidePersonalInfoBData GuideData = null;
            if (GuideID == 0)
            {
                GuideData = new GuidePersonalInfoBData();
                GuideData.StateID = new Dictionary<string, string>();
                GuideData.City = new Dictionary<int, string>();
            }
            else
            {
                GuideData = (from Guide in _context.tblGuides
                             where Guide.GuideID == GuideID
                             select new GuidePersonalInfoBData
                             {
                                 GuideID = Guide.GuideID,
                                 Address = Guide.Address,
                                 SocialSecurityNumber = Guide.SocialSecurityNumber,
                                 SelectedSex = Guide.Sex,
                                 SelectedCity = Guide.City,
                                 CityID = Guide.CityID,
                                 SelectedStateID = Guide.StateID,
                                 SelectedCountryID = Guide.Country,
                                 SelectedLCountryID = Guide.LCountryID,
                                 PostalCode = Guide.PostalCode,
                                 BirthDate = Guide.Birthdate,
                                 EMail = Guide.EmailName,
                                 SpouseName = Guide.SpouseName,
                                 EmrgcyContactName = Guide.EmrgcyContactName,
                                 EmrgcyContactPhone = Guide.EmrgcyContactPhone,
                             }).SingleOrDefault();
                if (GuideData.SelectedLCountryID == 1)
                {
                    GuideData.StateID = (from state in _context.tblStates where state.Active == true orderby state.StateID select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
                    if (GuideData.SelectedStateID == null)
                    {
                        GuideData.City = new Dictionary<int, string>();
                    }
                    else
                    {
                        GuideData.City = (from city in _context.tblCities where city.StateID == GuideData.SelectedStateID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
                        if (GuideData.City == null)
                            GuideData.City = new Dictionary<int, string>();
                        if (GuideData.CityID == null && GuideData.SelectedCity != null && GuideData.SelectedCity != "")
                        {
                            GuideData.City.Add(-1, GuideData.SelectedCity);
                            GuideData.CityID = -1;
                        }
                    }
                }
                else
                {
                    GuideData.StateID = new Dictionary<string, string>();
                    if (GuideData.SelectedLCountryID == null)
                        GuideData.City = new Dictionary<int, string>();
                    else
                    {
                        GuideData.City = (from city in _context.tblCities where city.CountryID == GuideData.SelectedLCountryID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
                        if (GuideData.City == null)
                            GuideData.City = new Dictionary<int, string>();
                        if (GuideData.CityID == null && GuideData.SelectedCity != null && GuideData.SelectedCity != "")
                        {
                            GuideData.City.Add(-1, GuideData.SelectedCity);
                            GuideData.CityID = -1;
                        }
                    }
                }
            }
            GuideData.CountryID = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.Nationality select new { Key = cnt.CountryID, Value = cnt.Nationality }).ToDictionary(o => o.Key, o => o.Value);
            GuideData.LCountryID = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.CountryName select new { Key = cnt.CountryID, Value = cnt.CountryName }).ToDictionary(o => o.Key, o => o.Value);
            GuideData.Sex = new Dictionary<int, string> { 
               {0, "Mr." },
               {1, "Mrs." },
               {2, "Ms." },
           };
            return GuideData;
        }
        public GuideAssignmentsBData LoadGuideAssignmentsByID(int GuideID)
        {
            GuideAssignmentsBData GuideAssingnmentsData = new GuideAssignmentsBData();
            GuideAssingnmentsData.Rows = new List<GuideAssignmentsRowBData>();
            GuideAssingnmentsData.Rows = (from Guide in _context.tblGuides
                                          from GuideConf in _context.tblGuideConfirms
                                          from Groups in _context.tblGroups
                                          from Customer in _context.tblCustomers
                                          where Guide.GuideID == GuideConf.GuideID
                                          && GuideConf.GroupID == Groups.GroupID
                                          && Groups.CustomerID == Customer.CustomerID
                                          && Groups.Cancelled == false
                                          && GuideConf.Selected == true
                                          && Customer.Active == true
                                          orderby Groups.FinalPayment
                                          select new GuideAssignmentsRowBData
                                          {
                                              CustomerCode = Customer.CustomerCode,
                                              GroupCode = Groups.GroupCode,
                                              GroupName = Groups.GroupName,
                                              Canceled = Groups.Cancelled,
                                              Selected = GuideConf.Selected,
                                              StartDate = GuideConf.StartDate,
                                              FinishDate = GuideConf.FinishDate,
                                          }).ToList();
            return GuideAssingnmentsData;
        }
        public ErrorBInfo SaveGuideCompanyInfoData(GuideCompanyInfoBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                var EditGuide = (from Guide in _context.tblGuides where Guide.GuideID == Data.GuideID select Guide).SingleOrDefault();
                if (EditGuide != null)
                {
                    EditGuide.FirstName = Data.FirstName;
                    EditGuide.LastName = Data.LastName;
                    EditGuide.MiddleName = Data.MiddleName;
                    EditGuide.DateHired = Data.DateHired;
                    EditGuide.Active = Data.Active;
                    EditGuide.IsFind = Data.IsFind;
                    EditGuide.Phone = Data.Phone;
                    EditGuide.Fax = Data.Fax;
                    EditGuide.RegionID = Data.SelectedRegionID;
                    EditGuide.AreaID = Data.SelectedAreaID;
                    EditGuide.Note = Data.Note;
                    EditGuide.Salary = Data.Salary;
                    _context.SaveChanges();
                }
                else
                {
                    Error.errorCode = 130;
                    Error.Message = "Guide Can`t be found:";
                }
            }
            catch (Exception ex)
            {
                Error.errorCode = 130;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public ErrorBInfo SaveGuidePersonalInfoData(GuidePersonalInfoBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                var EditGuide = (from Guide in _context.tblGuides where Guide.GuideID == Data.GuideID select Guide).SingleOrDefault();
                if (EditGuide != null)
                {
                    EditGuide.SocialSecurityNumber = Data.SocialSecurityNumber;
                    EditGuide.Sex = Data.SelectedSex;
                    EditGuide.Address = Data.Address;
                    EditGuide.City = Data.CityID == -1 ? Data.SelectedCity : (from city in _context.tblCities where city.CityID == Data.CityID select city.City).SingleOrDefault();
                    EditGuide.CityID = Data.CityID != -1 ? Data.CityID : null;
                    EditGuide.StateID = Data.SelectedStateID;
                    EditGuide.Country = Data.SelectedCountryID;
                    EditGuide.LCountryID = Data.SelectedLCountryID;
                    EditGuide.PostalCode = Data.PostalCode;
                    EditGuide.Birthdate = Data.BirthDate;
                    EditGuide.EmailName = Data.EMail;
                    EditGuide.SpouseName = Data.SpouseName;
                    EditGuide.EmrgcyContactName = Data.EmrgcyContactName;
                    EditGuide.EmrgcyContactPhone = Data.EmrgcyContactPhone;
                    _context.SaveChanges();
                }
                else
                {
                    Error.errorCode = 130;
                    Error.Message = "Guide Can`t be found:";
                }
            }
            catch (Exception ex)
            {
                Error.errorCode = 130;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public Dictionary<string, string> LoadState()
        {
            Dictionary<string, string> States = new Dictionary<string, string>();
            States = (from state in _context.tblStates where state.Active == true orderby state.StateID select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
            if (States == null)
            {
                States = new Dictionary<string, string>();
            }
            return States;
        }
        public Dictionary<int, string> LoadCity(int CountryID, string StateID)
        {
            Dictionary<int, string> City = new Dictionary<int, string>();
            if (CountryID != 0)
            {
                City = (from city in _context.tblCities where city.CountryID == CountryID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
            }
            else
            {
                City = (from city in _context.tblCities where city.StateID == StateID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
            }
            if (City == null)
                City = new Dictionary<int, string>();
            return City;
        }
        public void Dispose()
        {
            _context.Dispose();
        }
    }
}
